package com.fantuan.db.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Service;

import com.fantuan.db.bean.Group;

@Service
public class GroupDAO extends BaseDAO {

	public Group getGroup(int nid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select * from t_groupcenter where nid=?";

		Group result = null;
		try {

			Object[] objs = new Object[] { nid };

			result = (Group) this.query(conn, sql, new BeanHandler<Group>(
					Group.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	
	
	/**
	 * 获取用户所在饭团，退团的不算
	 * @param nid
	 * @return
	 * @throws SQLException
	 */
	public List<Group> getUserGroup(int userid) throws SQLException {

		Connection conn = dataSource.getConnection();

		String sql = "select a.* from t_groupcenter a,"
				+ "("
				+ "SELECT * FROM `t_yw_ftusermoney` WHERE `nUserID`=? and `nState`=0 order by nGroupID"
				+ ") b" + " where a.nid=b.`nGroupID`" + " order by a.nid desc";

		List<Group> result = null;
		try {

			Object[] objs = new Object[] { userid };

			result = (List<Group>) this.query(conn, sql, new BeanListHandler<Group>(
					Group.class), objs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.close(conn);
		}

		return result;
	}
	
	

	/**
	 * 新增饭团
	 * 
	 * @param group
	 * @return
	 * @throws SQLException
	 */
	public long addGroup(Group group) throws SQLException {

		String sql = "insert into t_groupcenter(`nGroupTypeID`,"
				+ "`sGroupName`,`nIsHidden`,`nConsumeTimes`,"
				+ "`dConsumeSumMoney`,`nCreator`,`sAddress`,"
				+ "`sDesc`,`sPhoto`,`sFetionuser`,`sFetionpassword`,"
				+ "`sAlertCron`,`sSuburl`,`nConsumeType`,`nstate`,"
				+ "`dcreateDate`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now())";

		long id = -1;
		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { group.getnGroupTypeID(),
					group.getsGroupName(), group.getnIsHidden(),
					group.getnConsumeTimes(), group.getdConsumeSumMoney(),
					group.getnCreator(), group.getsAddress(), group.getsDesc(),
					group.getsPhoto(), group.getsFetionuser(),
					group.getsFetionpassword(), group.getsAlertCron(),
					group.getsSuburl(), group.getnConsumeType(),
					group.getNstate() };
			this.update(conn, sql, objs);

			id = this.getLastInsertID(conn);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}

		return id;
	}
	
	
	
	public void updateGroup(Group group) throws SQLException {

		String sql = "update t_groupcenter set `nGroupTypeID`=?," +
				"`sGroupName`=?,`nIsHidden`=?,`nConsumeTimes`=?, " +
				"`dConsumeSumMoney`=?,`nCreator`=?,`sAddress`=?, " +
				"`sDesc`=?,`sPhoto`=?,`sFetionuser`=?, " +
				"`sFetionpassword`=?,`sAlertCron`=?,`sSuburl`=?, " +
				"`nConsumeType`=?,`nstate`=?,`dcreateDate`=?, `dupdateDate`=? " +
				"where `nID`=?";

		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { 
					group.getnGroupTypeID(),
					group.getsGroupName(),
					group.getnIsHidden(),
					group.getnConsumeTimes(),
					group.getdConsumeSumMoney(),
					group.getnCreator(),
					group.getsAddress(),
					group.getsDesc(),
					group.getsPhoto(),
					group.getsFetionuser(),
					group.getsFetionpassword(),
					group.getsAlertCron(),
					group.getsSuburl(),
					group.getnConsumeType(),
					group.getNstate(),
					group.getDcreateDate(),
					group.getDupdateDate(),
					group.getnID()
					};
			this.update(conn, sql, objs);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}
	}
	
	
	
	/**
	 * 更新饭团的消费总额和消费次数
	 * 根据日志计算得来
	 * @param groupid
	 * @throws SQLException
	 */
	public void updateGroupConsumeInfo(int groupid) throws SQLException {

		String sql = "update t_groupcenter a,"
				+ "("
				+ "SELECT count(*) zongshu,sum(`nAllMoney`) money,nGroupID FROM `t_yw_ftconsumelog` WHERE `nGroupID`=?"
				+ ") b"
				+ " set a.dConsumeSumMoney=b.money,a.nConsumeTimes=b.zongshu"
				+ " where a.nid = b.nGroupID";

		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			Object[] objs = new Object[] { 
					groupid
					};
			this.update(conn, sql, objs);

			DbUtils.close(conn);
		} catch (SQLException e) {
			DbUtils.close(conn);
			e.printStackTrace();
		}
	}
}